"""
视频材料工作流数据库服务

负责将视频材料工作流的执行结果保存到数据库
"""

import json
import hashlib
from pathlib import Path
from typing import Dict, Any, List, Optional
from datetime import datetime
from loguru import logger

from src.core.di import Service, Inject
from src.repositories.video_repository import VideoRepository
from src.models.video import Video, VideoMetadata, VideoClassification
from src.services.metadata_service import MetadataService


@Service("material_workflow_database_service")
class MaterialWorkflowDatabaseService:
    """
    视频材料工作流数据库服务
    
    功能：
    1. 解析工作流执行结果
    2. 提取视频和分类信息
    3. 保存到数据库
    4. 处理重复文件
    """

    def __init__(self, 
                 video_repo: VideoRepository = Inject(VideoRepository),
                 metadata_service: MetadataService = Inject(MetadataService)):
        self.logger = logger.bind(component="MaterialWorkflowDatabaseService")
        self.video_repo = video_repo
        self.metadata_service = metadata_service

    def save_workflow_results(self, workflow_result: Dict[str, Any]) -> Dict[str, Any]:
        """
        保存工作流执行结果到数据库
        
        Args:
            workflow_result: 工作流执行结果
            
        Returns:
            保存结果统计
        """
        self.logger.info("开始保存工作流结果到数据库")
        
        save_stats = {
            "total_videos": 0,
            "new_videos": 0,
            "updated_videos": 0,
            "skipped_videos": 0,
            "failed_videos": 0,
            "total_classifications": 0,
            "new_classifications": 0,
            "errors": []
        }

        try:
            # 从工作流结果中提取视频数据
            video_data_list = self._extract_video_data_from_result(workflow_result)
            save_stats["total_videos"] = len(video_data_list)
            
            self.logger.info(f"从工作流结果中提取到 {len(video_data_list)} 个视频")

            # 处理每个视频
            for video_data in video_data_list:
                try:
                    result = self._save_single_video(video_data)
                    
                    if result["action"] == "created":
                        save_stats["new_videos"] += 1
                    elif result["action"] == "updated":
                        save_stats["updated_videos"] += 1
                    elif result["action"] == "skipped":
                        save_stats["skipped_videos"] += 1
                    
                    save_stats["total_classifications"] += result.get("classifications_count", 0)
                    save_stats["new_classifications"] += result.get("new_classifications", 0)
                    
                except Exception as e:
                    self.logger.error(f"保存视频失败 {video_data.get('file_path', 'unknown')}: {e}")
                    save_stats["failed_videos"] += 1
                    save_stats["errors"].append({
                        "file_path": video_data.get("file_path", "unknown"),
                        "error": str(e)
                    })

            self.logger.info(f"工作流结果保存完成: {save_stats}")
            return save_stats

        except Exception as e:
            self.logger.error(f"保存工作流结果失败: {e}")
            save_stats["errors"].append({"general_error": str(e)})
            return save_stats

    def _extract_video_data_from_result(self, workflow_result: Dict[str, Any]) -> List[Dict[str, Any]]:
        """从工作流结果中提取视频数据"""
        video_data_list = []
        
        # 从报告中提取数据
        report = workflow_result.get("report", {})
        results = report.get("results", [])
        
        for result in results:
            segments_with_metadata = result.get("segments_with_metadata", [])
            
            for segment in segments_with_metadata:
                video_data = self._extract_single_video_data(segment)
                if video_data:
                    video_data_list.append(video_data)
        
        return video_data_list

    def _extract_single_video_data(self, segment: Dict[str, Any]) -> Optional[Dict[str, Any]]:
        """从单个片段数据中提取视频信息"""
        file_path = segment.get("final_path") or segment.get("file_path")
        
        if not file_path or not Path(file_path).exists():
            self.logger.warning(f"视频文件不存在: {file_path}")
            return None

        try:
            file_path_obj = Path(file_path)
            file_stats = file_path_obj.stat()
            
            # 计算文件哈希
            file_hash = self._calculate_file_hash(file_path)
            
            # 提取基本信息
            video_data = {
                "file_path": str(file_path),
                "file_name": file_path_obj.name,
                "file_size": file_stats.st_size,
                "file_hash": file_hash,
                "is_processed": True,
                "is_available": True,
                "last_accessed": datetime.now(),
            }
            
            # 提取元数据
            metadata = segment.get("metadata", {})
            if metadata:
                video_data.update({
                    "duration": metadata.get("duration", 0.0),
                    "width": metadata.get("width", 0),
                    "height": metadata.get("height", 0),
                    "fps": metadata.get("fps", 0.0),
                    "bitrate": metadata.get("bitrate", 0),
                    "codec": metadata.get("codec", ""),
                    "format": metadata.get("format", ""),
                })
                
                # 保存完整元数据
                video_data["full_metadata"] = metadata
            
            # 提取分类信息
            classification = segment.get("classification", {})
            if classification and not classification.get("error"):
                video_data["classification"] = {
                    "category": segment.get("target_category", "unclassified"),
                    "confidence": segment.get("confidence", 0.0),
                    "reasoning": classification.get("reasoning", ""),
                    "classifier_type": "gemini",
                    "model_name": classification.get("model_name", "gemini-1.5-flash"),
                    "raw_response": classification.get("raw_response", ""),
                    "processing_time": classification.get("processing_time", 0.0),
                }
            
            # 提取片段信息
            segment_info = segment.get("segment_info", {})
            if segment_info:
                video_data["segment_info"] = segment_info
            
            return video_data
            
        except Exception as e:
            self.logger.error(f"提取视频数据失败 {file_path}: {e}")
            return None

    def _calculate_file_hash(self, file_path: str) -> str:
        """计算文件SHA256哈希"""
        try:
            hash_sha256 = hashlib.sha256()
            with open(file_path, "rb") as f:
                for chunk in iter(lambda: f.read(4096), b""):
                    hash_sha256.update(chunk)
            return hash_sha256.hexdigest()
        except Exception as e:
            self.logger.warning(f"计算文件哈希失败 {file_path}: {e}")
            return ""

    def _save_single_video(self, video_data: Dict[str, Any]) -> Dict[str, Any]:
        """保存单个视频到数据库"""
        file_path = video_data["file_path"]
        file_hash = video_data["file_hash"]
        
        # 检查是否已存在（按路径或哈希）
        existing_video = None
        if file_hash:
            existing_video = self.video_repo.get_by_hash(file_hash)
        
        if not existing_video:
            existing_video = self.video_repo.get_by_path(file_path)
        
        result = {
            "action": "",
            "video_id": None,
            "classifications_count": 0,
            "new_classifications": 0
        }
        
        if existing_video:
            # 更新现有视频
            self._update_existing_video(existing_video, video_data)
            result["action"] = "updated"
            result["video_id"] = existing_video.id
            self.logger.info(f"更新现有视频: {file_path}")
        else:
            # 创建新视频
            new_video = self._create_new_video(video_data)
            result["action"] = "created"
            result["video_id"] = new_video.id
            self.logger.info(f"创建新视频: {file_path}")
            existing_video = new_video
        
        # 处理分类信息
        if "classification" in video_data:
            classification_result = self._save_video_classification(existing_video, video_data["classification"])
            result["classifications_count"] = 1
            result["new_classifications"] = 1 if classification_result["created"] else 0
        
        return result

    def _create_new_video(self, video_data: Dict[str, Any]) -> Video:
        """创建新视频记录"""
        # 准备视频数据
        video_kwargs = {
            "file_path": video_data["file_path"],
            "file_name": video_data["file_name"],
            "file_size": video_data["file_size"],
            "file_hash": video_data.get("file_hash"),
            "duration": video_data.get("duration"),
            "width": video_data.get("width"),
            "height": video_data.get("height"),
            "fps": video_data.get("fps"),
            "bitrate": video_data.get("bitrate"),
            "codec": video_data.get("codec"),
            "format": video_data.get("format"),
            "is_processed": video_data.get("is_processed", True),
            "is_available": video_data.get("is_available", True),
            "last_accessed": video_data.get("last_accessed")
        }

        # 保存到数据库
        saved_video = self.video_repo.create(**video_kwargs)

        # 创建元数据记录
        if "full_metadata" in video_data:
            self._create_video_metadata(saved_video, video_data["full_metadata"])

        return saved_video

    def _update_existing_video(self, video: Video, video_data: Dict[str, Any]) -> Video:
        """更新现有视频记录"""
        # 更新基本信息
        video.file_path = video_data["file_path"]
        video.file_name = video_data["file_name"]
        video.file_size = video_data["file_size"]
        video.is_processed = video_data.get("is_processed", True)
        video.is_available = video_data.get("is_available", True)
        video.last_accessed = video_data.get("last_accessed")
        
        # 更新视频属性（如果有新数据）
        if video_data.get("duration"):
            video.duration = video_data["duration"]
        if video_data.get("width"):
            video.width = video_data["width"]
        if video_data.get("height"):
            video.height = video_data["height"]
        if video_data.get("fps"):
            video.fps = video_data["fps"]
        if video_data.get("bitrate"):
            video.bitrate = video_data["bitrate"]
        if video_data.get("codec"):
            video.codec = video_data["codec"]
        if video_data.get("format"):
            video.format = video_data["format"]
        
        # 保存更新
        updated_video = self.video_repo.update(video)
        
        # 更新元数据
        if "full_metadata" in video_data:
            self._update_video_metadata(updated_video, video_data["full_metadata"])
        
        return updated_video

    def _create_video_metadata(self, video: Video, metadata: Dict[str, Any]) -> VideoMetadata:
        """创建视频元数据记录"""
        metadata_kwargs = {
            "video_id": video.id,
            "raw_metadata": json.dumps(metadata, ensure_ascii=False)
        }

        # 这里可以添加更多元数据字段的处理
        # 例如从metadata中提取特定字段

        return self.video_repo.create_metadata(**metadata_kwargs)

    def _update_video_metadata(self, video: Video, metadata: Dict[str, Any]) -> VideoMetadata:
        """更新视频元数据记录"""
        if video.video_metadata:
            video.video_metadata.raw_metadata = json.dumps(metadata, ensure_ascii=False)
            video.video_metadata.updated_at = datetime.now()
            return self.video_repo.update_metadata(video.video_metadata)
        else:
            return self._create_video_metadata(video, metadata)

    def _save_video_classification(self, video: Video, classification_data: Dict[str, Any]) -> Dict[str, Any]:
        """保存视频分类信息"""
        # 重新获取视频对象以确保有正确的会话
        video_with_classifications = self.video_repo.get_with_classifications(video.id)

        # 检查是否已存在相同的分类
        existing_classification = None
        if video_with_classifications and video_with_classifications.classifications:
            for cls in video_with_classifications.classifications:
                if (cls.classifier_type == classification_data.get("classifier_type", "gemini") and
                    cls.category == classification_data.get("category")):
                    existing_classification = cls
                    break
        
        if existing_classification:
            # 更新现有分类
            existing_classification.confidence = classification_data.get("confidence", 0.0)
            existing_classification.reasoning = classification_data.get("reasoning", "")
            existing_classification.model_name = classification_data.get("model_name", "")
            existing_classification.processing_time = classification_data.get("processing_time", 0.0)
            existing_classification.updated_at = datetime.now()
            
            self.video_repo.update_classification(existing_classification)
            return {"created": False, "classification_id": existing_classification.id}
        else:
            # 创建新分类
            classification_kwargs = {
                "video_id": video.id,
                "category": classification_data.get("category", "unclassified"),
                "confidence": classification_data.get("confidence", 0.0),
                "classifier_type": classification_data.get("classifier_type", "gemini"),
                "model_name": classification_data.get("model_name", ""),
                "reasoning": classification_data.get("reasoning", ""),
                "processing_time": classification_data.get("processing_time", 0.0)
            }

            saved_classification = self.video_repo.create_classification(**classification_kwargs)
            return {"created": True, "classification_id": saved_classification.id}

    def get_workflow_statistics(self) -> Dict[str, Any]:
        """获取工作流处理统计信息"""
        stats = {}
        
        # 总视频数
        total_videos = self.video_repo.count_all()
        stats["total_videos"] = total_videos
        
        # 已处理视频数
        processed_videos = self.video_repo.count_by_field("is_processed", True)
        stats["processed_videos"] = processed_videos
        
        # 分类统计
        classification_stats = self.video_repo.get_classification_statistics()
        stats["classification_distribution"] = classification_stats
        
        # 最近处理的视频
        recent_videos = self.video_repo.get_recent_videos(limit=10)
        stats["recent_videos"] = [
            {
                "file_name": video.file_name,
                "file_path": video.file_path,
                "duration": video.duration,
                "created_at": video.created_at.isoformat() if video.created_at else None
            }
            for video in recent_videos
        ]
        
        return stats
